

package com.mexcad.db.connectors;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mexcad.db.dto.CatEstado;
import com.mexcad.db.dto.Cliente;
import com.mexcad.db.dto.Comisionista;
import com.mexcad.db.dto.Usuario;
import com.mexcad.db.utils.ExceptionDB;
import com.mexcad.db.utils.HibernateUtil;

public class ComisionistasConnector {


 
	
 private static Log logger = LogFactory.getLog(ComisionistasConnector.class);

	public ComisionistasConnector(){
		logger.debug("Instanciating ComisionistasConnector");
	}
	
	
	
	

	public static List<Comisionista> getAllComisionistas(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Comisionista>lista=null;
		try{
			logger.debug("Querying Comisionista");
			String query ="FROM Comisionista";
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	public static Comisionista getComisionistaPorId(int idComisionista){
		Session session = HibernateUtil.getSessionFactory().openSession();
		logger.debug("Querying Ejecutivo");
		Comisionista comisionista= null;
		try{
			String query =" FROM  Comisionista where idComisionista="+idComisionista;
			comisionista= (Comisionista)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return comisionista;
	}
	
	
	public static synchronized int guardaComisionista(Comisionista comisionista){
		Session session=null;
		Transaction transaction=null;
		try{

			session = HibernateUtil.getSessionFactory().openSession();
			transaction = session.beginTransaction();
			comisionista.setNombres(comisionista.getNombres().toUpperCase());
			comisionista.setApellidoPaterno(comisionista.getApellidoPaterno().toUpperCase());
			comisionista.setApellidoMaterno(comisionista.getApellidoMaterno().toUpperCase());
			comisionista.setRfc(comisionista.getRfc().toUpperCase());
			

				
			logger.debug("Querying Ejecutivo");
			if(comisionista.getIdComisionista() == 0){
				comisionista.setIdComisionista(getUltimoIdComisionista()+1);
				session.save(comisionista);
			}else{
				session.update(comisionista);
			}
			transaction.commit();
			session.close();
			
			return comisionista.getIdComisionista();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			transaction.rollback();
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(excepts);
		}catch(Exception e){
			transaction.rollback();
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(e);
		}finally {
			if(session!= null&& session.isOpen())
				session.close();
			
		}
		
	}
	

	
	




	public static   int getUltimoIdComisionista(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Integer max =null;
		try{
			String query = "select max(idComisionista) from Comisionista ";
		
			max = (Integer) session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(excepts);
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(e);
		}finally {
			if(session!= null&& session.isOpen())
				session.close();
			
		}
		if(max==null)
			return 0;
		else
			return max;
		
	}
	
	public static synchronized void eliminaComisionista(int idComisionista) {
		Session session = null;
		Transaction transaction =null;
		
		try{
			 session = HibernateUtil.getSessionFactory().openSession();
			 transaction = session.beginTransaction();
			
			Comisionista ejecutivo = getComisionistaPorId(idComisionista);
	
			session.delete(ejecutivo);
			transaction.commit();
	
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			transaction.rollback();
			
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			transaction.rollback();
			
			if(session!= null&& session.isOpen())
				session.close();

		}finally {
			if(session!= null&& session.isOpen())
				session.close();
			
		}
	}	
	
	/*
	public static CtCvesProgoPerAnualNER getCtCvesProgoPerAnualByProgNER(CtClavesPoaPK pkAfec,String re, String rs, String actividad){
		logger.debug("Querying CtCvesProgoPerAnual");
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CtCvesProgoPerAnualNER where anio="+pkAfec.getAnioId()+" AND "+
						"sector='"+pkAfec.getSectorId()+"' AND  "+
						"subsector='"+pkAfec.getSubsectorId()+"' AND  "+
						"unidadr='"+pkAfec.getUnidadId()+"' AND  "+
						"re='"+re+"' AND  "+
						"sr='"+rs+"' AND " +
						"ai='"+actividad+"'" ;
		
		CtCvesProgoPerAnualNER clavePOA=(CtCvesProgoPerAnualNER)session.createQuery(query).uniqueResult();
		session.close();	
		
		return clavePOA;
	}



   public static List<AfectacionesStatus> getAllAfectacionesStatus(){
			logger.debug("Querying AfectacionesStatus");
			Session session = HibernateUtil.getSessionFactory().openSession();
			String query ="FROM AfectacionesStatus";
			List <AfectacionesStatus>lista=session.createQuery(query).list();
			return lista;
   }
   
*/
	public static void main(String []arg){
		
		//List <Cliente>lista2=ClienteConnector.getClientesPorEstado(1);
	//	System.out.println("parentCount: "+GerenteConnector.getStateCount());
		
		
	}
	
}
